﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Light.Utility
{
    /// <summary>
    /// 基于OleDb的Excel读取写类
    /// </summary>
    internal class ExcelReader : IDisposable
    {

        private OleDbConnection _connect;
        private readonly string _excelFileName;
        private bool _headers = true;
        private bool _mixedData;
        private string _sheetName;
        private string _sheetRange = string.Empty;


        public ExcelReader(string fileName)
        {
            this._excelFileName = fileName;
        }

        public void Close()
        {
            if (this._connect != null)
            {
                if (this._connect.State != ConnectionState.Closed)
                {
                    this._connect.Close();
                }
                this._connect.Dispose();
                this._connect = null;
            }
        }

        public void Dispose()
        {
            if (this._connect != null)
            {
                this._connect.Dispose();
                this._connect = null;
            }
        }

        private string ExcelConnection()
        {
            string connectionString = null;
            string extension = this._excelFileName;

            switch (extension)
            {
                case ".xlsx":
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this._excelFileName + ";Extended Properties='Excel 12.0;" + this.ExcelConnectionOptions() + "'";
                    break;
                default:
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this._excelFileName + ";Extended Properties='Excel 8.0;" + this.ExcelConnectionOptions() + "'";
                    break;
            }
            //链

            return connectionString;
        }

        private string ExcelConnectionOptions()
        {
            string str = "";
            if (this._mixedData)
            {
                str = str + "IMEX=1;";
            }
            if (this._headers)
            {
                return (str + "HDR=Yes;");
            }
            return (str + "HDR=No;");
        }

        public static string GetColumnName(int columnIndex)
        {
            if (columnIndex < 0x1a)
            {
                return Convert.ToString(Convert.ToChar((int)(Convert.ToByte('A') + columnIndex)));
            }
            int num = columnIndex / 0x1a;
            int num2 = columnIndex % 0x1a;
            return (Convert.ToString((int)(Convert.ToByte('A') + num)) + Convert.ToString((int)(Convert.ToByte('A') + num2)));
        }

        public static int GetColumnNumber(string column)
        {
            column = column.ToUpper();
            if (column.Length > 1)
            {
                return (Convert.ToInt16((int)(Convert.ToByte(column[1]) - 0x41)) + (Convert.ToInt16((int)(Convert.ToByte(column[1]) - 0x40)) * 0x1a));
            }
            return Convert.ToInt16((int)(Convert.ToByte(column[0]) - 0x41));
        }

        private OleDbType GetColumnType(DataColumn column)
        {
            switch (column.DataType.ToString())
            {
                case "System.DateTime":
                    return OleDbType.Date;

                case "System.Boolean":
                    return OleDbType.Boolean;

                case "System.Decimal":
                    return OleDbType.Currency;

                case "System.Double":
                    return OleDbType.Double;

                case "System.Single":
                    return OleDbType.Single;

                case "System.SByte":
                    return OleDbType.TinyInt;

                case "System.Int16":
                    return OleDbType.SmallInt;

                case "System.Int32":
                    return OleDbType.Integer;

                case "System.Int64":
                    return OleDbType.BigInt;
            }
            return OleDbType.VarChar;
        }

        private string GetColumnTypeString(DataColumn column)
        {
            switch (this.GetColumnType(column))
            {
                case OleDbType.Date:
                    return "DATETIME";

                case OleDbType.Boolean:
                    return "BIT";

                case OleDbType.Currency:
                    return "MONEY";

                case OleDbType.Double:
                    return "FLOAT";

                case OleDbType.Single:
                    return "REAL";

                case OleDbType.TinyInt:
                    return "TINYINT";

                case OleDbType.SmallInt:
                    return "SMALLINT";

                case OleDbType.Integer:
                case OleDbType.BigInt:
                    return "INTEGER";
            }
            return "CHAR";
        }

        public string[] GetSheetNames()
        {
            string[] strArray2;
            if ((this._connect == null) || (this._connect.State != ConnectionState.Open))
            {
                throw new Exception("Connection is unassigned or closed.");
            }
            DataTable oleDbSchemaTable = null;
            try
            {
                oleDbSchemaTable = this._connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (oleDbSchemaTable == null)
                {
                    return null;
                }
                string[] strArray = new string[oleDbSchemaTable.Rows.Count];
                int index = 0;
                foreach (DataRow row in oleDbSchemaTable.Rows)
                {
                    string str = row["TABLE_NAME"].ToString().Replace("'", ""); //当Sheet列包含特殊字符时，OldDb会自动加上单引号,影响后面的读取，在此替换掉. HY
                    if (str.Contains("$"))
                    {
                        continue;
                    }
                    strArray[index] = str;//.Substring(0, str.Length - 1);
                    index++;
                }
                strArray2 = strArray;
            }
            catch (Exception)
            {
                strArray2 = null;
            }
            finally
            {
                if (oleDbSchemaTable != null)
                {
                    oleDbSchemaTable.Dispose();
                    oleDbSchemaTable = null;
                }
            }
            return strArray2;
        }

        public void Open()
        {
            if (this._connect != null)
            {
                if (this._connect.State == ConnectionState.Open)
                {
                    this._connect.Close();
                }
                this._connect = null;
            }
            this._connect = new OleDbConnection(this.ExcelConnection());
            this._connect.Open();
        }

        public DataTable ReadData()
        {
            DataTable table2;
            if ((this._connect == null) || (this._connect.State != ConnectionState.Open))
            {
                throw new Exception("Connection is unassigned or closed.");
            }
            if ((this._sheetName == null) || (this._sheetName.Length == 0))
            {
                throw new Exception("SheetName was not assigned.");
            }
            using (OleDbCommand command = new OleDbCommand())
            {

                command.CommandText = "SELECT * FROM [" + this._sheetName + "$" + this._sheetRange + "]";
                command.Connection = this._connect;
                using (OleDbDataAdapter adapter = new OleDbDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable dataTable = new DataTable(this._sheetName);
                    adapter.FillSchema(dataTable, SchemaType.Source);
                    adapter.Fill(dataTable);
                    if (!this._headers && (this._sheetRange.IndexOf(":") > 0))
                    {
                        int columnNumber = GetColumnNumber(this._sheetRange.Substring(0, this._sheetRange.IndexOf(":") - 1));
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            dataTable.Columns[i].Caption = GetColumnName(columnNumber + i);
                        }
                    }
                    table2 = dataTable;
                }
            }
            return table2;
        }

        public void WriteData(DataTable table)
        {
            if ((this._connect == null) || (this._connect.State != ConnectionState.Open))
            {
                throw new Exception("Connection is unassigned or closed.");
            }
            if (table == null)
            {
                throw new ArgumentNullException("table");
            }
            if (table.Columns.Count != 0)
            {
                string strA = "[" + table.TableName.Trim() + "]";
                if (((string.Compare(strA, "TABLE", true) == 0) || (string.Compare(strA, "USER", true) == 0)) || (string.Compare(strA, "GROUP", true) == 0))
                {
                    strA = "_" + strA;
                }
                if (Array.IndexOf<string>(this.GetSheetNames(), strA) >= 0)
                {
                    throw new Exception("Sheet '" + table.TableName + "' is already exists.");
                }
                using (OleDbCommand command = new OleDbCommand())
                {
                    int num2;
                    DataColumn column;
                    string str3;
                    StringBuilder builder = new StringBuilder();
                    builder.Append("CREATE TABLE ");
                    builder.Append(strA);
                    builder.Append(" (");
                    int count = table.Columns.Count;
                    for (num2 = 0; num2 < count; num2++)
                    {
                        column = table.Columns[num2];
                        string columnTypeString = this.GetColumnTypeString(column);
                        str3 = "[" + column.ColumnName + "]";
                        if (num2 < (count - 1))
                        {
                            builder.Append(string.Format("{0} " + columnTypeString + ", ", str3));
                        }
                        else
                        {
                            builder.Append(string.Format("{0} " + columnTypeString + ")", str3));
                        }
                    }
                    command.CommandText = builder.ToString();
                    command.Connection = this._connect;
                    command.ExecuteNonQuery();
                    builder = new StringBuilder();
                    builder.Append("INSERT INTO ");
                    builder.Append(strA);
                    builder.Append(" (");
                    for (num2 = 0; num2 < count; num2++)
                    {
                        str3 = "[" + table.Columns[num2].ColumnName + "]";
                        if (num2 < (count - 1))
                        {
                            builder.Append(str3 + ",");
                        }
                        else
                        {
                            builder.Append(str3 + ") VALUES (");
                        }
                    }
                    for (num2 = 0; num2 < count; num2++)
                    {
                        if (num2 < (count - 1))
                        {
                            builder.Append("@PAR_" + num2.ToString() + ", ");
                        }
                        else
                        {
                            builder.Append("@PAR_" + num2.ToString() + ")");
                        }
                    }
                    command.CommandText = builder.ToString();
                    OleDbParameterCollection parameters = command.Parameters;
                    num2 = 0;
                    while (num2 < count)
                    {
                        column = table.Columns[num2];
                        OleDbType columnType = this.GetColumnType(column);
                        parameters.Add(new OleDbParameter("@PAR_" + num2.ToString(), columnType));
                        num2++;
                    }
                    foreach (DataRow row in table.Rows)
                    {
                        for (num2 = 0; num2 < parameters.Count; num2++)
                        {
                            if (((table.Columns[num2].DataType == typeof(string)) && (row[num2] != null)) && (row[num2] != DBNull.Value))
                            {
                                string s = (string)row[num2];
                                if (s.Length > 0xff)
                                {
                                    parameters[num2].Value = StringHelper.Truncate(s, 0xff);
                                }
                                else
                                {
                                    parameters[num2].Value = s;
                                }
                            }
                            else
                            {
                                parameters[num2].Value = row[num2];
                            }
                        }
                        command.ExecuteNonQuery();
                    }
                }
            }
        }


        public string FileName
        {
            get
            {
                return this._excelFileName;
            }
        }

        public bool Headers
        {
            get
            {
                return this._headers;
            }
            set
            {
                this._headers = value;
            }
        }

        public bool MixedData
        {
            get
            {
                return this._mixedData;
            }
            set
            {
                this._mixedData = value;
            }
        }

        public string SheetName
        {
            get
            {
                return this._sheetName;
            }
            set
            {
                this._sheetName = value;
            }
        }

        public string SheetRange
        {
            get
            {
                return this._sheetRange;
            }
            set
            {
                if (value.IndexOf(":") == -1)
                {
                    throw new Exception("Invalid range length");
                }
                this._sheetRange = value;
            }
        }
    }


}
